from sqlalchemy import Column, Integer, String, update, DateTime, func, and_, desc

from model.sqlConnect import Base, SqliteSqlalchemy

"""
字典表
"""


class Dict(Base):
    # 定义表名
    __tablename__ = 'DICT'
    # 定义字段
    id_dict = Column(Integer, autoincrement=True, nullable=False, primary_key=True)
    type = Column(String(20), nullable=False, index=True)
    code = Column(String(20), nullable=False)
    name = Column(String(50), nullable=False)
    is_deleted = Column(String(2), default="N")
    create_at = Column(DateTime, nullable=False, default=func.now())
    update_at = Column(DateTime, nullable=False, default=func.now(), server_onupdate=func.now())

    def __repr__(self):
        return f"Dict: id_dict={self.id_dict}, type={self.type}, code={self.name}"


def queryAll(_dict: Dict):
    session = SqliteSqlalchemy().session
    result = session.query(Dict) \
        .filter(and_(Dict.is_deleted == "Y"),
                and_(Dict.id_dict == _dict.id_dict, _dict is not None),
                and_(Dict.name.like(f"%{_dict.name}%"))) \
        .order_by(desc(Dict.update_at)).all()
    session.close()
    return result


def insert(_dict: Dict):
    session = SqliteSqlalchemy().session
    session.add(_dict)
    session.commit()
    session.close()


def update(_dict: Dict):
    session = SqliteSqlalchemy().session
    queryparam = {}
    for key, value in _dict.__dict__.items():
        if value is not None and key != "_sa_instance_state":
            queryparam.setdefault(key, value)
    print(queryparam)
    session.query(Dict).where(Dict.id_dict == _dict.id_dict).update(queryparam)
    session.commit()
    session.close()


def remove(id_dict: int):
    update(Dict(id_dict=id_dict, is_deleted="Y"))
